package com.realtyManage.springboot.Dao;

import com.realtyManage.springboot.Model.Do.ApplyInfo;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface ApplyInfoRepository extends JpaRepository<ApplyInfo,Integer>, JpaSpecificationExecutor<ApplyInfo> {


    @Query(value = "SELECT COUNT(1) FROM  apply_info WHERE user_id = ?1 AND virtual_number = ?2 AND step NOT IN(4,5)",nativeQuery = true)
    int verification(int userId, String phone);

    @Query(value = "SELECT COUNT(1) FROM  apply_info WHERE virtual_number = ?1 AND step NOT IN(4,5)",nativeQuery = true)
    int verification(String phone);

    @Modifying
    @Transactional
    @Query("update ApplyInfo a set " +
            "a.acceptanceId =  CASE WHEN :#{#applyInfo.acceptanceId} IS NULL THEN a.acceptanceId ELSE :#{#applyInfo.acceptanceId} END ," +
            "a.acceptanceName =  CASE WHEN :#{#applyInfo.acceptanceName} IS NULL THEN a.acceptanceName ELSE :#{#applyInfo.acceptanceName} END ," +
            "a.step =  CASE WHEN :#{#applyInfo.step} IS NULL THEN a.step ELSE :#{#applyInfo.step} END " +
            "where a.id = :#{#applyInfo.id}")
    int updateApplyInfo(@Param("applyInfo") ApplyInfo applyInfo);

    @Query(value = "SELECT * FROM  apply_info WHERE acceptance_id = ?1",nativeQuery = true)
    List<ApplyInfo> scanRecord(Integer uid);

    @Query(value = "SELECT * FROM  apply_info WHERE virtual_number = ?1",nativeQuery = true)
    ApplyInfo queryApplyInfo(String xnh);

    @Query(value = "SELECT * FROM `apply_info` WHERE step IN (0,1,5)",nativeQuery = true)
    List<ApplyInfo> queryApplyYGQ();

    @Query(value = "SELECT id FROM `apply_info` WHERE virtual_number = ?1 AND step != 4",nativeQuery = true)
    int queryId(String virtualNumber);

    @Query(value = "SELECT * FROM `apply_info` WHERE id = ?1",nativeQuery = true)
    List<ApplyInfo> queryApplyDetailById(String id);

    @Modifying
    @Transactional
    @Query(value = "delete from apply_info where id = ?1",nativeQuery = true)
    int deleteInvalidApply (String id);
}
